BigQuery のテーブルに検索インデックスを作成してみる

#Google Cloud (GCP)

#Google BigQuery

hanzawa.yuya

2024.08.26

こんにちは、Google Cloud データエンジニアのはんざわです。
先日、BigQuery の 検索インデックス（Search Index） を使用する機会があり、その際に得られた知見を本ブログで紹介します。

この記事では、検索インデックスをテーブルに作成する方法や注意点を紹介します。
検索インデックスを参照する方法などは、次回のブログで紹介します。

検索インデックスとは？

検索インデックスとは、BigQuery で非構造のテキストデータや半構造の JSON データから特定の文字を高性能に検索および抽出する機能です。
事前に特定のカラムにインデックスを作成し、SQL の WHERE 句において SEARCH 関数 を使うことで検索インデックスが利用されます。

本機能は、2022年10月27日に正式リリース(GA)されました。

October 27, 2022

従来は検索インデックスを利用するために SEARCH 関数を使う必要がありましたが、2024年3月28日のアップデートにより、 IN や = でも検索インデックスが適用されるようになりました。
これにより、普段よく使用される演算子でも検索インデックスを利用できるようになりました。

March 28, 2024

検索インデックスの作成方法

CREATE SEARCH INDEX の構文を使用して、指定したカラムにインデックスを適用することができます。

CREATE SEARCH INDEX [ IF NOT EXISTS ] index_name
ON table_name({ALL COLUMNS | column_name [, ...]})
[OPTIONS(index_option_list)]

参考元：CREATE SEARCH INDEX statement

また、インデックスを適用可能なカラムの型は以下の通りです。

STRING
INT64（プレビュー）
TIMESTAMP（プレビュー）
ARRAY
STRUCT
JSON

検索インデックスを作成してみる

ここからは、様々な種類のテーブルに検索インデックスを作成できるか検証してみたいと思います。
検証するテーブルの種類は、以下の通りです。

標準テーブル
外部テーブル
一時テーブル
サブクエリ(CTEs）
ビュー
マテリアライズドビュー

標準テーブル

まずは、標準テーブルに検索インデックスを作成してみます。

以下のクエリを使用して、commits_index テーブルの message カラムに message_index の検索インデックスを作成します。

CREATE SEARCH INDEX message_index
ON github_repos_index.commits_index(message);

> テーブル <PROJECT_ID>.github_repos_index.commits_index の検索インデックスを作成しました。

試しにインデックスを作成した直後に、以下のクエリで message カラムから Merge の文字列を検索してみましょう。

SELECT
  message
FROM
  github_repos_index.commits_index
WHERE
  SEARCH(message, 'Merge')

しかし、クエリの実行情報を確認すると、インデックス使用のモード が UNUSED になっており、これでは検索インデックスが使用されていません。
また、インデックスが使用されていない理由 は、以下のようになっていました。

The creation of search index `message_index` of the base table `<PROJECT_ID>:github_repos_index.commits_index` has not been completed.

スクリーンショット 2024-08-18 2.02.48

どうやら 「インデックスを作成するクエリの完了 = インデックスの作成完了」ではないようです。
実際には、クエリ完了後にしばらくしてからインデックスが作成されるみたいです。

しばらくしてから再度実行すると、以下のように インデックス使用のモード が FULLY_USED になっており、インデックスが正常に使用されていることが確認できました。

スクリーンショット 2024-08-18 23.33.08

外部テーブル

次に、標準テーブル以外のテーブルに検索インデックスを作成できるか検証してみます。
検証するテーブルの種類は、以下の通りです。

外部テーブル
一時テーブル
サブクエリ(CTEs）
ビュー
マテリアライズドビュー

まずは、外部テーブルで試してみます。

CREATE SEARCH INDEX external_index
ON sample_index.sample_external(comment);

> Cannot create search index on table of type EXTERNAL

ドキュメントには特に記載はありませんでしたが、どうやら 外部テーブルに検索インデックスを作成することはできない ようです。

一時テーブル

次は、一時テーブルに検索インデックスを作成してみます。

CREATE TEMP TABLE wikipedia AS (
  SELECT
    comment
  FROM
    samples.wikipedia
);

CREATE SEARCH INDEX sample_index
ON wikipedia(comment);

テーブル <PROJECT_ID>._script581a08ef0298ea178e2edc51771ef6f318d9af71.wikipedia の検索インデックスを作成しました。

意外なことに、一時テーブルに対して検索インデックスを作成するクエリが通りました。
前述のとおり、インデックスの作成には多少のラグがあるため、15分間の待機時間を設定してから検索を試みます。

/* 変数を定義 */
DECLARE DELAY_TIME DATETIME;
DECLARE WAIT BOOL;

/* 一時テーブルを作成 */
CREATE TEMP TABLE wikipedia AS (
  SELECT
    comment
  FROM
    samples.wikipedia
);

/* 検索インデックスを作成 */
CREATE SEARCH INDEX sample_index
ON wikipedia(comment);

/* 15分のsleep */
SET WAIT = TRUE;
SET DELAY_TIME = DATE_ADD(CURRENT_DATETIME, INTERVAL 900 SECOND);
WHILE WAIT DO
  IF (DELAY_TIME < CURRENT_DATETIME) THEN
    SET WAIT = FALSE; 
  END IF; 
END WHILE;

/* 検索してみる */
SELECT
  *
FROM
  wikipedia
WHERE
  SEARCH(comment, 'hoge');

以下のキャプチャのように、15分経ってもインデックスの作成が完了しないことが確認できます。
その後、30分と60分でも試してみましたが、いずれも同じ理由で検索インデックスが使用されていませんでした。

どうやら 検索インデックスを作成するクエリは実行できるが、実際にはインデックスが作成されない と考えられます。

スクリーンショット 2024-08-24 1.28.05

The creation of search index `sample_index` of the base table `<PROJECT_ID>:_script2c9f449faccd3e3347a571f98c328f520fd99b7d.wikipedia` has not been completed.

サブクエリ

次は、サブクエリに検索インデックスを作成してみます。

結論として、サブクエリに検索インデックスを作成することはできません。

以下のようなクエリを試してみましたが、全てでシンタックスエラーが発生しました。

## ケース1
WITH sample_cte AS (
  SELECT
    comment
  FROM
    sample_index.wikipedia
)

CREATE SEARCH INDEX sample_index
ON sample_cte(comment);

## ケース2
CREATE SEARCH INDEX sample_index
WITH sample_cte AS (
  SELECT
    comment
  FROM
    sample_index.wikipedia
)
ON sample_cte(comment);

## ケース3
CREATE SEARCH INDEX sample_index
ON (SELECT comment FROM sample_index.wikipedia)(comment);

ビュー

次は、ビューに検索インデックスを作成してみます。

CREATE SEARCH INDEX view_index
ON sample_index.sample_view(comment);

> Cannot create search index on table of type VIEW

外部テーブルと同様に ビューに検索インデックスを作成することはできません。

ドキュメントにも以下のような制限が記載されており、ビューに直接インデックスを作成することはできません。

検索インデックスをビューまたはマテリアライズドビューに直接作成することはできませんが、インデックス付きテーブルのビューに対して SEARCH 関数を呼び出すと、基盤となる検索インデックスを使用できます。

参考元：制限事項

ビューの元となるベーステーブルに検索インデックスを作成することで、ビューを参照する際にそのインデックスが利用されます。

マテリアライズドビュー

最後に、マテリアライズドビューに検索インデックスを作成してみます。

CREATE SEARCH INDEX materialized_view_index
ON sample_index.sample_materialized_view(comment);

> Cannot create search index on table of type MATERIALIZED_VIEW

ビューと同様に マテリアライズドビューに検索インデックスを作成することはできません。

ドキュメントにも以下のような制限が記載されており、マテリアライズドビューに直接インデックスを作成することはできません。

クエリでマテリアライズドビューを参照している場合、検索インデックスは使用されません。

参考元：制限事項

通常のビューであれば、構成しているベーステーブルに検索インデックスを作成することで参照時にインデックスが使用されますが、マテリアライズドビューでは使用されません。

試しに、マテリアライズドビューのベーステーブルにインデックスを作成し、参照してみました。
以下のキャプチャのように インデックス使用のモード が UNUSED になり、 インデックスが使用されていない理由 は、次のようになっていました。

The search query has been optimized with materialized view of the base table <PROJECT_ID>:sample_index.wikipedia_index.

スクリーンショット 2024-08-22 22.07.08

あくまで筆者の推測ですが、マテリアライズドビューは登録したクエリの結果をキャッシュとして、ベーステーブルとは別のストレージ領域に保存されますが、キャッシュには検索インデックスが反映されないのではないかと推測しています。

検索インデックスを削除する方法

検索インデックスを削除する方法は2つあります。

1つ目は、インデックスが適用されているテーブルを削除する方法です。
テーブルを削除するとインデックスも自動的に削除されます。

2つ目は、DROP SEARCH INDEX の構文を使用する方法です。

DROP SEARCH INDEX test on samples.github_timeline

> テーブル <PROJECT_ID>.samples.github_timeline の検索インデックスがドロップされました。

参考：検索インデックスを削除する

共通テーブル式（CTE）と一時テーブルの補足

インデックスを作成した標準テーブルを共通テーブル式（CTE）と一時テーブルで読み込み、それらを参照した場合にインデックスが適用されるか確認してみます。

具体的には、以下のようなクエリを想定しています。

共通テーブル式（CTE）

WITH sample_index AS (
  SELECT
    *
  FROM
    sample_index.wikipedia_index
)

SELECT
  *
FROM
  sample_index
WHERE
  SEARCH(comment, 'fuga')

一時テーブル

CREATE TEMP TABLE sample_index AS (
  SELECT
    *
  FROM
    sample_index.wikipedia_index
);

SELECT
  *
FROM
  sample_index
WHERE
  SEARCH(comment, 'fuga')

結論から言うと、共通テーブル式（CTE）ではインデックスが適用され、一時テーブルではインデックスが適用されません。
一時テーブルは、内部で別のテーブルが作成されるため、インデックスが適用されません。
また、一時テーブルに検索インデックスを作成することはできないため、検索インデックスを使用したい場合は、一時テーブルの利用を避けた方が良いでしょう。

その他の制限と推奨事項

制限

検索インデックスの作成後にテーブルの名前を変更するとインデックスは無効になる
- 参考元：制限事項
テーブルのサイズが 10 GB 未満の場合、検索インデックスは使用されない
- 参考元：インデックスの更新について
組織ごとにインデックスが適用されたテーブルの合計サイズに上限があります。US や EU のマルチリージョンは 100 TB、その他すべてのリージョンは 20 TB
- 参照元：割り当てと上限 / 検索インデックス

推奨事項

検索インデックスは、テーブルのサイズが大きいほどパフォーマンスが向上する
- 参考元：ベストプラクティス
一意の値が少ないカラムをインデックスに登録しないほうが良い
- 参考元：ベストプラクティス

検証結果のまとめ

CREATE SEARCH INDEX を実行してから、検索インデックスが作成されるまで多少のラグがある
標準テーブル以外のテーブルには、検索インデックスを作成することはできない
一時テーブルでは、検索インデックスを作成するクエリは実行できるが、実際にはインデックスが作成されない
ビューの元となるベーステーブルに検索インデックスを作成することで、ビューを参照する際にそのインデックスが使用される
- マテリアライズドビューでは、インデックスは利用されない
共通テーブル式（CTE）では、インデックスが適用されるが、一時テーブルでは適用されない